import os
import sys
import md5
from pysqlite2 import dbapi2 as sqlite
import pysqlite2
import l_time
import l_soal
import traceback
try:
    import config
    import view
    import error
except:
    if str(os.path.dirname(__file__)).lower() == str(os.getcwd()).lower():
        pass
    else:
        print "\n"
        print " Import Error ! \n"
        print " TraceBack : ", traceback.format_exc()

class temp_ver(object):
    def __init__(self,parent=None, mid=None, name=None, passwd=None, status=None, pledge=None, role=None, access=None, where=None, orderby=None):

        super(temp_ver, self).__init__()
        #object.__init__(self)

        self.mid = mid
        self.name = name
        self.passwd = passwd
        self.status = status
        self.pledge = pledge
        self.role = role
        self.access = access
        self.where = where
        self.orderby = orderby

        self.conn = sqlite.connect(os.path.join(os.environ['TEMP'],'temp.db'))
        #print os.path.join(os.environ['TEMP'],'temp.db')
        self.curs = self.conn.cursor()    

        self.sql0 = '''DROP TABLE IF EXISTS login'''
        self.sql1 = '''DROP TABLE IF EXISTS lastlogin'''
        #self.sql1A = '''CREATE TABLE [login] (
        #		[id] BIGINT(100) NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY, 
        #		[name] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, 
        #		[password] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, 
        #		[status] VARCHAR(2) NOT NULL ON CONFLICT ROLLBACK,
        #                [pledge] VARCHAR(2) NOT NULL On CONFLICT ROLBACK,
        #                [role] VARCHAR(2) NOT NULL ON CONFLICT ROLLBACK,
        #		[daccess] TEXT NOT NULL ON CONFLICT ROLLBACK)'''
        #
        self.sql1A = '''CREATE TABLE [login] (
			[id] BIGINT(100) NOT NULL PRIMARY KEY, 
			[name] VARCHAR(100) NOT NULL, 
			[password] VARCHAR(100) NOT NULL, 
			[status] VARCHAR(2) NOT NULL,
                        [pledge] VARCHAR(2) NOT NULL,
                        [role] VARCHAR(2) NOT NULL,
			[daccess] TEXT NOT NULL)'''

        #self.sql1B = '''CREATE TABLE [lastlogin] (
        #		[id] BIGINT(100) NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY, 
        #		[name] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, 
        #		[password] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, 
        #		[status] VARCHAR(2) NOT NULL ON CONFLICT ROLLBACK,
        #		[daccess] TEXT NOT NULL ON CONFLICT ROLLBACK)'''

        self.sql1B = '''CREATE TABLE [lastlogin] (
			[id] BIGINT(100) NOT NULL PRIMARY KEY, 
			[name] VARCHAR(100) NOT NULL, 
			[password] VARCHAR(100) NOT NULL, 
			[status] VARCHAR(2) NOT NULL,
			[daccess] TEXT NOT NULL)'''
        
        self.sql1C = '''CREATE TABLE IF NOT EXISTS [id_temp] (
			[idA] BIGINT(100) NOT NULL PRIMARY KEY, 
                        [idB] BIGINT(100)'''

            #self.sql2 = 'INSERT INTO login VALUES(' + str(self.mid) + ",'" + str(self.name) + "','" + str(self.passwd) + "','" + str(self.status) + "','" +  str(self.gettime()) + "')"
            #self.sql2b = 'INSERT INTO login VALUES(' + str(self.getmax()) + ",'" + str(self.name) + "','" + str(self.passwd) + "','" + str(self.status) + "','" +  str(self.gettime()) + "')"
            #self.sql2 = 'INSERT INTO login VALUES(1,"kevin","blackid","1","2011-05-31 05:03:00")' #for test only
            #self.sql3 = "SELECT * FROM login WHERE name = '" + self.name + "' AND password = '" + str(self.passwd) + "'" + " LIMIT 1"

    def msg_error(self,info,data):
        mod = error.e_login
        msg = mod.elogin(self)
        msg.setInfo(info)
        msg.setData(data)
        msg.show()
        del(msg)

    def gettime(self):
        data = l_time.l_time()
        return data.gettime()

    def gettimedateA(self, name, passwd):
        try:
            #data = list(self.getdata("kevin","blackid"))[0]     #for test only
            data = list(self.getdata(name, passwd))[0]
            data002 = data[4]
            #print "data = ", data
            #print "data002 = ", data002
            datax = str(data002).split(" ")
            #print  "datax = ", datax
            return datax
        except IndexError, e:
            data_e = traceback.format_exc()
            data = "No data found !, please insert data before ! "
            error.main('info',str(data)+"\n"+"-"*51+"\n"+str(data_e))

    def createfirst(self):
        try:
            self.curs.execute(self.sql0)
            self.conn.commit()

        except pysqlite2._sqlite.OperationalError, e:
            self.curs.execute(self.sql1A)
            self.conn.commit()

    def insertdata(self, table, idx=None, name=None, passwd=None, status=None, pledge=None, role=None, access=None):  
        if passwd == None:
            self.passwd = md5.new(self.passwd).hexdigest()
        else:
            self.passwd = md5.new(passwd).hexdigest()
        if name == None:
            self.name = self.name
        else:
            self.name = name
        if status == None:
            self.status = self.status
        else:
            self.status = status
        if pledge == None:
            self.pledge = self.pledge
        else:
            self.pledge = pledge
        if role == None:
            self.role = self.role
        else:
            self.role = role
        if access == None:
            access = str(self.gettime())
        else:
            access = self.access
        try:
            self.curs.execute(self.sql1A)
            self.conn.commit()
        except pysqlite2._sqlite.OperationalError, e:
            pass

        try:
            sql2b = 'INSERT INTO ' + table + ' VALUES(' + str(self.getmax()) + ",'" + str(self.name) + "','" + str(self.passwd) + "','" + str(self.status) + "','" + str(pledge) + "','" + str(role) + "','" +  access + "')"
            sql2 = 'INSERT INTO ' + table + ' VALUES(' + idx + ",'" + str(self.name) + "','" + str(self.passwd) + "','" + str(self.status) + "','" + str(pledge) + "','" + str(role) + "','" +  access + "')"
            self.curs.execute(sql2)
            self.conn.commit()
            return True
        except pysqlite2._sqlite.OperationalError, e:
            try:
                self.curs.execute(self.sql1A)
                self.conn.commit()
                self.curs.execute(sql2)
                self.conn.commit()
            except:
                data_e = traceback.format_exc()
                return ['error',str(data_e)]
        except pysqlite2._sqlite.IntegrityError, e:
            try:
                self.curs.execute(sql2b)
                self.conn.commit()
            except:
                data_e = traceback.format_exc()
                return ['error',str(data_e)]
        finally:
            data_e = traceback.format_exc()
            return ['error',str(data_e)]
        
    def insert(self, table, data):
        self.curs.execute(self.sql1C)
        self.conn.commit()
        sql = "INSERT INTO "  + str(table) + " VALUES(" + str(data)  + ")"
        self.curs.execute(sql)
        self.conn.commit()

    def insertdatalast(self, idx, name, passwd, status, pledge, role, access):  
        try:
            self.curs.execute(self.sql1B)
            self.conn.commit()
            #return True
        except pysqlite2._sqlite.OperationalError, e:
            pass

        try:
            sql2 = 'INSERT INTO login VALUES('+ str(idx) + ",'" + str(name) + "','" + md5.new(str(passwd)).hexdigest() + "','" + str(status) + "','" + str(pledge) + "','" + str(role) + "','" + str(access) + "')"
            self.curs.execute(sql2)
            self.conn.commit()
            return True
        except pysqlite2._sqlite.OperationalError, e:
            #data_e = traceback.format_exc()
            #print "ERROR = ", str(data_e)
            try:
                self.curs.execute(self.sql1A)
                self.conn.commit()
                self.curs.execute(sql2)
                self.conn.commit()
            except:
                #data_e = traceback.format_exc()
                #print "ERROR = ", str(data_e)
                #return ['error',str(data_e)]
                pass
        except pysqlite2._sqlite.IntegrityError, e:
            data_e = traceback.format_exc()
            #error.main('error',str(data))
            #self.msg_error("error",str(data))
            return ['error',str(data_e)]
        except TypeError, e:
            data_e = traceback.format_exc()
            return ['error',str(data_e)]
        except NameError, e:
            data_e = traceback.format_exc()
            return ['error',str(data_e)]

    def verify(self):    
        sql3 = "SELECT * FROM login WHERE name = '" + self.name + "' AND password = '" + str(self.passwd) + "'" + " LIMIT 1"

        self.curs.execute(sql3)
        self.conn.commit()    

        data = self.curs.fetchall()
        return data

    def getdata(self, name=None, passwd=None):
        if name == None:
            self.name = self.name
        else:
            self.name = name 
        if  passwd == None:
            self.passwd = md5.new(self.passwd).hexdigest()
        else:
            self.passwd = md5.new(passwd).hexdigest()

        try:
            sql4 = "SELECT * FROM login WHERE name = '" + self.name + "' AND password = '" + self.passwd + "' LIMIT 1"
            self.curs.execute(sql4)
            self.conn.commit()    
            return True
        except pysqlite2._sqlite.OperationalError, e:
            try:
                self.curs.execute(self.sql1A)
                self.conn.commit()  
                self.curs.execute(sql4)
                self.conn.commit()  
            except:
                data_e = traceback.format_exc()
                return ['error',str(data_e)]

        data = self.curs.fetchall()
        return data

            #for row in curs:         #for test only
            #    print row            #for test only
            
    def getAll_F(self, table):
        try:
            sql4 = "SELECT * FROM " + str(table)
            self.curs.execute(sql4)
            self.conn.commit()    
            data = self.curs.fetchall()
            return data
        except pysqlite2._sqlite.OperationalError, e:
            try:
                self.curs.execute(self.sql1A)
                self.conn.commit()  
                self.curs.execute(sql4)
                self.conn.commit()  
                data = self.curs.fetchall()
                return data
            except:
                data_e = traceback.format_exc()
                return ['error',str(data_e)]
            
    def getAll(self, table):
        try:
            sql4 = "SELECT * FROM " + str(table)
            self.curs.execute(sql4)
            self.conn.commit()    
            data = self.curs.fetchall()
            return data
        except:
            data_e = traceback.format_exc()
            return ['error',str(data_e)]
        
    def getAll_byid(self, table, idx):
        try:
            sql4 = "SELECT * FROM " + str(table) + " WHERE id = " + str(idx)
            self.curs.execute(sql4)
            self.conn.commit()    
            data = self.curs.fetchall()
            return data
        except:
            data_e = traceback.format_exc()
            return ['error',str(data_e)]
            
    def getpledge(self,name,passwd,pledge='pledge'):
        if name == None:
            self.name = self.name
        else:
            self.name = name 
        if  passwd == None:
            self.passwd = md5.new(self.passwd).hexdigest()
        else:
            self.passwd = md5.new(passwd).hexdigest()
        try:
            sql4 = "SELECT " + pledge + " FROM login WHERE name = '" + self.name + "' AND password = '" + self.passwd + "' LIMIT 1"
            self.curs.execute(sql4)
            self.conn.commit()    
            data = self.curs.fetchall()
            return data
        except pysqlite2._sqlite.OperationalError, e:
            try:
                self.curs.execute(self.sql1A)
                self.conn.commit()  
                self.curs.execute(sql4)
                self.conn.commit()  
            except:
                data_e = traceback.format_exc()
                return ['error',str(data_e)]

    def getmax(self):
        try:
            sql = "SELECT count(id) FROM login"
            self.curs.execute(sql)
            self.conn.commit()

            data = self.curs.fetchall()
            data2 = data[0][0]
            maxid = data2 + 1
            return maxid
        except:
            data_e = traceback.format_exc()
            self.curs.execute(self.sql1A)
            self.conn.commit()
            self.curs.execute(self.sql1B)
            self.conn.commit()

    def clearbyname(self):
        sql1 = "DELETE FROM login WHERE name = '" + str(self.name) + "'"
        sql2 = "DELETE FROM lastlogin"
        self.curs.execute(sql1)
        self.conn.commit()
        self.curs.execute(sql2)
        self.conn.commit()
        view.v_login.main()

    def getall(self):
        sql = "SELECT * FROM login "
        try:
            self.curs.execute(sql)
            self.conn.commit()    
        except pysqlite2._sqlite.OperationalError, e:
            pass

        data = self.curs.fetchall()
        return data

    def getlast(self):
        sql = "SELECT * FROM lastlogin "
        try:
            self.curs.execute(sql)
            self.conn.commit()    
        except pysqlite2._sqlite.OperationalError, e:
            pass

        data = self.curs.fetchall()
        return data

    def cekcookies(self, name, passwd):
        data = self.gettimedateA(name, passwd)
        time = l_time.l_time()
        try:
            dtime = data[1]
            data001 = time.setseconds(dtime)
            data002 = time.getseconds()
            data003 = int(config.max_time)
            data004 = int(data002 - data001)  #different
            #print data001            #for test only
            #print data002            #for test only
            #print data003            #for test only
            #print data004            #for test only

            ddate = data[0]
            ddate001 = time.setdate(ddate)
            ddate002 = time.getdate()


            if ddate001[0] == ddate002[0]:
                if ddate001[1] == ddate002[1]:
                    if ddate001[2] == ddate002[2]:
                        if data004 > data003:
                            self.logout()
                            #view.e_login.main() # you not login
                            #return False
                        else:
                            if list(app.getlast()) == []:
                                view.e_login.main() #you have been logout
                                #return False
                            else:
                                #return True
                                pass
                    else:
                        self.clearbyname()
                else:
                    self.clearbyname()
            else:
                self.clearbyname()
        except TypeError, e:
            dataA = traceback.format_exc()
            dataB = "You must login to access this system again !"
            error.main('error',str("Programming Development : \n\n" + dataA))
            error.main('Info',str(dataB))
            view.v_login.main()

    def generate_hash(self, dhash):
        data = str(md5.new(dhash).hexdigest())
        print "dhash = ", dhash
        return data

    def logout(self):
        try:
            #sql0 = '''DROP TABLE IF EXISTS soal'''
            sql0 = '''DELETE FROM soal'''
            self.curs.execute(self.sql0)
            self.conn.commit()
            self.curs.execute(self.sql1)
            self.conn.commit()
            self.curs.execute(sql0)
            self.conn.commit()
            #os.remove(os.environ['TEMP'] + '\\' + 'temp.db')
        except:
            #data_e = traceback.format_exc()
            #print data_e
            #exc, err, traceback = sys.exc_info()
            #print "Traceback = ", exc, traceback.tb_frame.f_code.co_filename
            pass
    
    def getid_temp(self):
        try:
            self.curs.execute(self.sql1C)
            self.conn.commit()
            myclass_1 = l_soal.l_soal()
            id_max = myclass_1.getmax_id()
            sql = '''INSERT INTO id_temp VALUES(' ''' + str(id_max) + "','" + str(int(id_max) + 1) + "')"
            self.curs.execute(sql)
            self.conn.commit()
        except:
            dataA = traceback.format_exc()
            print "ERROR = ", dataA
            
        
if __name__ == "__main__":
    myclass = temp_ver()
    data = ['1','2']
    data_01 = str(",").join(data)
    myclass.insert('id_temp',data_01)



